Snowflake connector
The connector can be used to mask table residing under Snowflake platform. The Snowflake unload service splits data exported from source tables into smaller chunks (CSV files) and passes them onto the masking service. After the masking is completed, the files are sent to the Snowflake load service, which imports the masked files into the target database table.
Supported versions
The Snowflake platform is version-agnostic. We do not need to manage or specify snowflake database versions, as updates and maintenance are handled automatically by Snowflake.
Prerequisites
-
For accessing Snowflake warehouse, you will need a service account user with key-pair authentication. (Key-pair authentication provides enhanced security).
-
To configure key-pair authentication for a Snowflake service account, refer to the instructions provided in Snowflake’s configuring key-pair authentication documentation.
-
Use the following commands to generate base64 encoded value of encrypted private key and passphrase.
To generate base64 encoded value of encrypted private key:
echo -n `cat /home/delphix/keys/MKK_TEST_SFHSC_ADMIN_key.p8|base64`To generate base64 encode value of passphrase:
echo -n <passphrase>|base64 -w 0-
Configure Snowflake storage integration to read data from and write data to an Amazon S3 bucket referenced in an external (i.e. S3) stage
-
To configure the Snowflake storage integration, refer to the instructions provided in Snowflake’s configuring a Snowflake storage integration to access Amazon S3 documentation.
-
Configure Snowflake external stage (i.e. S3) where the data files are staged.
-
To configure the Snowflake storage integration, refer to the instructions provided in Snowflake’s creating an S3 stage documentation.
Roles and privileges
-
The table below lists all the privileges required to unload and load data from/to Snowflake platform.
-
The sample names (e.g., DLPX_WAREHOUSE, DLPX_SNOW_DB, DLPX_SNOWHSC, etc.) are for example and explanation purposes only.
-
Replace these names with your actual warehouse, database, schema, and role names as per your Snowflake environment.
Source Snowflake database
| Object type | Object name | Privilege | Granted to role |
|---|---|---|---|
| Warehouse | DLPX_WAREHOUSE | USAGE, OPERATE | DLPX_SNOWHSC |
| Schema | DLPX_SNOW_SCHEMA | USAGE | DLPX_SNOWHSC |
| External Stage | DLPX_SNOW_DB.DLPX_SNOW_SCHEMA.DLPX_S3_EXT_STAGE | USAGE | DLPX_SNOWHSC |
| Database | DLPX_SNOW_DB | USAGE | DLPX_SNOWHSC |
| All Tables | DLPX_SNOW_DB.DLPX_SNOW_SCHEMA | SELECT | DLPX_SNOWHSC |
Target Snowflake database
| Object type | Object name | Privilege | Granted to role |
|---|---|---|---|
| Warehouse | DLPX_WAREHOUSE | USAGE, OPERATE | DLPX_SNOWHSC |
| Database | DLPX_SNOW_DB | USAGE | DLPX_SNOWHSC |
| Schema | DLPX_SNOW_SCHEMA | USAGE, MODIFY, CREATE TABLE | DLPX_SNOWHSC |
| All Tables | DLPX_SNOW_DB.DLPX_SNOW_SCHEMA | SELECT, INSERT, UPDATE, DELETE | DLPX_SNOWHSC |
| External Stage | DLPX_SNOW_DB.DLPX_SNOW_SCHEMA.DLPX_S3_EXT_STAGE | USAGE | DLPX_SNOWHSC |
Warehouse privileges: USAGE allows the role to use the warehouse and OPERATE enables suspending and resuming it.
Database privileges: USAGE allows the role to see the database and use objects within.
Schema privileges: Additional permissions such as CREATE TABLE and MODIFY are granted for schema-level management.
Table privileges: SELECT, INSERT, UPDATE, DELETE permissions for all existing tables ensure role-based access for managing data in the schema.
Stage privileges: USAGE is necessary for unloading data to and loading data from the external stage.
Property values
Snowflake Hyperscale Connector currently supports only AWS S3 as staging area.
Mandatory changes are required for the Snowflake Connector in the values.yaml file:
| Property | Value |
|---|---|
| stagingStorageType | AWS_S3 |
| applicationName | <staging-area-name> |
| authMechanism | AWS_ROLE |
| awsBucketName | <aws-s3-bucket-name> |
| awsBucketRegion | <s3-region-name> |
| awsBucketDelimiter | / |
For instructions on how to configure AWS S3 as staging area, see Configuring AWS S3 bucket as staging area
Mandatory changes are required for the Snowflake connector in the values-snowflake.yaml file:
| Property | Value |
|---|---|
| sourceKeyFieldNames | database_name,schema_name,table_name,stage_name |
| loadServiceRequirepostload | false |
| validateUnloadRowCountForStatus | false |
| validateMaskedRowCountForStatus | false |
| validateLoadRowCountForStatus | false |
| displayBytesInfoInStatus | true |
| displayRowCountInStatus | true |
| snowflakePrivateKey | <SNOWFLAKE_PRIVATE_KEY> |
| snowflakePassphrase | <SNOWFLAKE_PASSPHRASE> |
| unload: imageName | delphix-snowflake-unload-service-app |
| load: imageName: | delphix-snowflake-load-service-app |
For default values, see Configuration settings.
Known limitation
In-Place Masking is not supported.